package com.jane.addressbook.manager;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.jane.addressbook.bean.Addressbook;

public class AddressbookManager implements Manager<Addressbook> {

	private Connection conn;

	public AddressbookManager(Connection conn) {
		this.conn = conn;
	}

	@Override
	public boolean add(Addressbook object) {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("insert into addressbook(id,name,tel1,tel2,sex,birthday,email,adress) values(?,?,?,?,?,?,?,?)");
			
			String date = object.getBirthday();
			Date birthday = null;
			if(date!=""){
				birthday = Date.valueOf(date);
			}
			ps.setInt(1, object.getId());
			ps.setString(2, object.getName());
			ps.setString(3, object.getTel1());
			ps.setString(4, object.getTel2());
			ps.setString(5, object.getSex());
			ps.setDate(6, birthday);
			ps.setString(7, object.getEmail());
			ps.setString(8, object.getAdress());
			ps.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public boolean delete(int id) {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("delete from addressbook where id = ?");
			ps.setInt(1, id);
			ps.execute();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public boolean update(Addressbook object) {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("update addressbook set name = ?,tel1 = ?,tel2 = ?,sex = ?,birthday = ?,email = ?,adress = ? where id = ?");
			
			String date = object.getBirthday();
			Date birthday = null;
			if(date!=""){
				birthday = Date.valueOf(date);
			}
			
			ps.setString(1, object.getName());
			ps.setString(2, object.getTel1());
			ps.setString(3, object.getTel2());
			ps.setString(4, object.getSex());
			ps.setDate(5, birthday);
			ps.setString(6, object.getEmail());
			ps.setString(7, object.getAdress());
			ps.setInt(8, object.getId());
			ps.execute();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public List<Addressbook> query() {
		// TODO Auto-generated method stub
		Statement stmt = null;
		List<Addressbook> result = new ArrayList<Addressbook>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from addressbook");
			while(rs.next()){
				Addressbook ab = new Addressbook(rs.getInt("id"), rs.getString("name"), 
						rs.getString("tel1"),rs.getString("tel2"),rs.getString("sex"),
						rs.getString("birthday"),rs.getString("email"),rs.getString("adress"));
				result.add(ab);
			}
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null){
				try{
					stmt.close();
				}catch(SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	@Override
	public Addressbook queryById(int id) {
		// TODO Auto-generated method stub
		Statement stmt = null;
		Addressbook ab = null;	
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from addressbook where id = "+id);
			if(rs.next()){
				ab = new Addressbook(rs.getInt("id"), rs.getString("name"), 
						rs.getString("tel1"),rs.getString("tel2"),rs.getString("sex"),
						rs.getString("birthday"),rs.getString("email"),rs.getString("adress"));
			}
			return ab;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			
		return null;
	}
	
	public int getMaxId(){
		Statement stmt = null;
		int id = 0;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select MAX(id) from addressbook");
			if(rs.next()){
				id = rs.getInt(1);
			}
			return id;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return 0;
		
	}
	 

}
